package com.tentact.coolmeetingtest.dao;

import com.tentact.coolmeetingtest.support.VoAutoBuilder;
import com.tentact.coolmeetingtest.util.JDBCUtil;
import com.tentact.coolmeetingtest.util.OtherUtil;
import com.tentact.coolmeetingtest.vo.Emp;
import com.tentact.coolmeetingtest.vo.Meeting;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author 星环
 * @date 2023/2/5 20:55:29
 * @description
 */
public class MeetingDao {
    public int addMeeting(Meeting meeting) {
        PreparedStatement preparedStatement = JDBCUtil.getPreparedStatementWithGeneratedKeys("insert into meeting values(null,?,?,?,?,?,?,?,?,?)",
                meeting.getMeetingName(), meeting.getRoomno(), meeting.getStartTime(),
                meeting.getEndTime(), meeting.getBookTime(), meeting.getExpectedPersonCount(), meeting.getDescription(),
                meeting.getState(), meeting.getBookerno());
        ResultSet r = null;
        try {
            int i = preparedStatement.executeUpdate();
            r = preparedStatement.getGeneratedKeys();

            r.next();
            return r.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(preparedStatement,r);
        }
        return -1;
    }

    public int addMember(int meetingno, Integer emp) {
        return (Integer)JDBCUtil.excute("insert into meeting_emp values(?,?)", Integer.class, meetingno, emp);
    }

    public int deleteMember(int meetingno, Integer integer) {
        return (Integer) JDBCUtil.excute("delete from meeting_emp where meetingno = ? and empno = ?", Integer.class, meetingno, integer);

    }

    public int deleteMeetingByMeetingno(int meetingno) {
        return (Integer) JDBCUtil.excute("delete from meeting where meetingno = ?", Integer.class, meetingno);
    }


    public List<Meeting> getMeetingInFuture7DaysByEmpno(int userno) {

        String sql = """
                select meetingno,meeting_name,start_time,end_time,room_name,m.state as state from meeting as m inner join meeting_room as r on m.roomno = r.roomno where
                meetingno in
                (
                select meetingno from meeting_emp
                where empno = ?
                ) and start_time < date_add(now(),interval 7 day) and start_time > now()
                
                """;

        PreparedStatement ps = JDBCUtil.getPreparedStatement(sql, userno);
        ResultSet resultSet;
        try {
            resultSet = ps.executeQuery();
            List<Meeting> meetingList = new VoAutoBuilder<Meeting>().buildAll(resultSet,Meeting.class,"meetingno","meeting_name","start_time","end_time","room_name","state");
            return meetingList;
        } catch (SQLException e){
            e.printStackTrace();
        }


        return null;
    }

    public Meeting getMeetingByMeetingno(int meetingno) {
        List<Meeting> meetingList = (List<Meeting>)JDBCUtil.excute("select * from meeting where meetingno = ?",Meeting.class,meetingno);
        return OtherUtil.getFirstDefaultNull(meetingList);
    }

    public List<Emp> getMembersByMeetingno(int meetingno) {
        String sql = """
                    select * from emp
                    where empno in (
                        select empno from meeting_emp
                        where meetingno = ?)
                """;
        return (List<Emp>) JDBCUtil.excute(sql,Emp.class,meetingno);
    }

    public List<Meeting> getActiveMeetingByBookerNo(int bookerno) {
        return (List<Meeting>) JDBCUtil.excute("select m.*,r.room_name from meeting m join meeting_room r on m.roomno = r.roomno where bookerno = ? and m.state = 1",Meeting.class,bookerno);
    }


    public int cancleMeeting(int meetingno) {
        return (Integer) JDBCUtil.excute("update meeting set state = 0 where meetingno = ?", Integer.class, meetingno);

    }

    public List<Meeting> getFullDetailedMeetingsByAttemptedEmpno(int empno) {
        String sql = """
                select m.*,r.room_name,e.emp_name as booker_name from meeting m join meeting_room r on m.roomno = r.roomno join emp e on m.bookerno = e.empno
                where m.meetingno in (
                select meetingno from meeting
                where empno = ?)
                """;
        return (List<Meeting>) JDBCUtil.excute(sql,Meeting.class,empno);
    }
}
